#-*- coding: utf-8 -*-

##Para contar tags
import urllib2
from BeautifulSoup import BeautifulSoup

def urlCountTag( tag, url ):
    html = urllib2.urlopen( url )
    soup = BeautifulSoup(''.join(html))
    print "{0}: {1}".format( tag, len( soup.findAll( tag ) ) )
    pass

def makeHTMLPage(name, title, bodyHTML):
    ficheiro = file( name, "w" )

    beginHTML = "<html><head><title>{0}</title></head><body>".format( title )
    endHTML = "</body></html>"
    
    pageHTML = beginHTML + bodyHTML + endHTML
    pageHTML = BeautifulSoup( ''.join( pageHTML ) )
    pageHTML = pageHTML.prettify()
    
    ficheiro.write( pageHTML )
    ficheiro.close()
    pass

#http://www.w3schools.com/html/html_tables.asp
def tableHTML(title, data):
    title = '''<font size="3" color="red" face="verdana"> {0} </font>'''.format( title )
    table = "<table border = 2>"
    
    for i in range( 0, len ( data ) ):
        table +="<tr>"
        for j in range( 0, len( data[ i ] ) ):
            table += "<td> {0} </td>".format( data[ i ][ j ] )
            pass
        table += "</tr>"
        pass
    table += "</table>"
    return title + table
    pass

##urlCountTag( "h1", "http://www.nocktv.com" )
##urlCountTag( "h2", "http://www.nocktv.com" )
##urlCountTag( "h3", "http://www.nocktv.com" )
##urlCountTag( "div","http://www.nocktv.com" )
##urlCountTag( "p",  "http://www.nocktv.com" )
##urlCountTag( "ul", "http://www.nocktv.com" )
##urlCountTag( "ol", "http://www.nocktv.com" )

#Para ler .csv e criar database com esses valores

import csv
import sqlite3
from unicodedata import normalize

##http://www.python.org.br/wiki/RemovedorDeAcentos
def normalizeString( txt, codif='utf-8' ):
    return normalize( 'NFKD', txt.decode( codif ) ).encode( 'ASCII','ignore' )

spamReader = csv.reader( open( '{0}'.format( '/home/admin1/Shared/CPython_Project/rebcsv/reb4.csv' ), 'rb' ), delimiter = ',' )

data = []
for row in spamReader:
    data.append( row )
    pass

for i in range( 0, len( data ) ):
    for j in range(0, len( data[ i ] )):
        data[ i ][ j ] = normalizeString( data[ i ][ j ] )
        pass
    pass

conn = sqlite3.connect( "rebides.db" )
c = conn.cursor()

c.execute( '''create table if not exists teachers ("{0}" integer, "{1}" text, "{2}" text, "{3}" text, "{4}" text, "{5}" text, "{6}" integer, "{7}" text, "{8}" text)'''
	  .format(data[ 0 ][ 0 ], data[ 0 ][ 1 ], data[ 0 ][ 2 ], data[ 0 ][ 3 ], data[ 0 ][ 4 ], data[ 0 ][ 5 ], data[ 0 ][ 6 ], data[ 0 ][ 7 ], data[ 0 ][ 8 ] ) )

conn.close()

conn = sqlite3.connect( "rebides.db" )
c = conn.cursor()

for i in range( 1, len (data ) ):
    c.execute('''insert into teachers ( "Codigo do Docente", "Nome", "Ultimo Grau", "Curso", "Categoria", "Regime de Tempo", "Ano Lectivo", "Tipo de Estabelecimento", "Nome do Estabelecimento" )
		values ("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}")'''
	      .format(data[ i ][ 0 ].replace( "\"", "'" ), data[ i ][ 1 ].replace( "\"", "'" ), data[ i ][ 2 ].replace( "\"", "'" ), data[ i ][ 3 ].replace( "\"", "'" ),
              data[ i ][ 4 ].replace( "\"", "'" ), data[ i ][ 5 ].replace( "\"", "'" ), data[ i ][ 6 ].replace( "\"", "'" ), data[ i ][ 7 ].replace( "\"", "'" ), data[ i ][ 8 ].replace( "\"", "'" ) ) )
    pass
conn.commit()
conn.close()

##ficheiro = file( "prof.text", "w" )
##for x in range( 0, 9 ):
##    ficheiro.write( data[ 0 ][ x ] + " | " )
##    pass
##ficheiro.close()


##Fazer consultas
##conn = sqlite3.connect( "rebides.db" )
##c = conn.cursor()
##
##names = []
##c.execute( '''select distinct "Nome do Estabelecimento" from teachers''' )
##
##for row in c:
##    names.append( [row[ 0 ]] )
##    pass
##
##
##years = []
##c.execute( '''select distinct "Ano Lectivo" from teachers''' )
##
##for row in c:
##    years.append( row[ 0 ] )
##    pass
##
##
##categorys = []
##c.execute( '''select distinct "Categoria" from teachers''' )
##
##for row in c:
##    categorys.append( row[ 0 ] )
##    pass
##
##categorys = []
##c.execute( '''select distinct "Categoria" from teachers''' )
##
##for row in c:
##    categorys.append( row[ 0 ] )
##    pass
##
##grade = []
##c.execute( '''select distinct "Ultimo Grau" from teachers''' )
##
##for row in c:
##    grade.append( row[ 0 ] )
##    pass
##
##counter = []
##t0 = time.time()
##title = "Number of teachers in the higher education system per establishment per year"
##for i in range ( 0, 6 ):#len( names ) ):
##    for j in range( 0, len( years ) ):
##        names[ i ].append( ( len(c.execute( '''select * from teachers where "Nome do Estabelecimento" = "{0}" AND "Ano Lectivo" = "{1}"'''
##                        .format( names[ i ][ 0 ], years[ j ])).fetchall() ) ) )
##        
##        if ( names[ i ][0] > 0 ):
##            print ( names[ i ] )
##            pass
##        pass
##    pass
####
####makeHTMLPage( "statistics.html", "Statistics", tableHTML(title, names) )
##
##
######number of teachers divided per categories per establisment and per year
##for i in range ( 0, 1):#len( names ) ):
##    for j in range( 0, len( years ) ):
##        for k in range( 0, len ( categorys ) ):
##            counter = len( c.execute( '''select * from teachers where "Nome do Estabelecimento" = "{0}" AND "Ano Lectivo" = "{1}" AND "Categoria" = "{2}"'''
##                        .format( names[ i ][0], years[ j ], categorys[ k ] ) ).fetchall() )
##            if ( counter > 0 ):
##                print ( names[ i ][0],categorys[ k ], counter )
##                pass
##            pass
##        pass
##    pass
##
######number of teachers divided per type of degree (doctorate level, masters level and graduate level) per establishment and per year)
##for i in range ( 0, 1 ):#len( names ) ):
##    for j in range( 0, len( years ) ):
##        for k in range( 0, len ( grade ) ):
##            counter = len(c.execute( '''select * from teachers where "Nome do Estabelecimento" = "{0}" AND "Ano Lectivo" = "{1}" AND "Ultimo Grau" = "{2}"'''
##                        .format( names[ i ][0], years[ j ], grade[ k ] ) ).fetchall() )
##            if ( counter > 0 ):
##                print ( names[ i ],grade[ k ], counter )
##                pass
##            pass
##        pass
##    pass
##print time.time() - t0
